{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import requests\n",
    "from tqdm import tqdm\n",
    "import os\n",
    "from io import StringIO\n",
    "from pathlib import Path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "savedir = Path(\"./annotated_coeqtl_snps/ldtrait\")\n",
    "\n",
    "celltypesnps = {}\n",
    "merged_dict = pd.read_excel('/groups/umcg-lld/tmp01/projects/1MCellRNAseq/GRN_reconstruction/ongoing/coeqtl_mapping/output/summary/coeQTLs_6majorcelltypes.filtered.xlsx',\n",
    "                         sheet_name=None)\n",
    "for celltype in merged_dict.keys():\n",
    "    celltypesnps[celltype] = list(merged_dict[celltype]['SNP'].unique())\n",
    "allcelltypes_snps = list(set([ele for l in celltypesnps.values() for ele in l]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "72"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(allcelltypes_snps)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 17%|█▋        | 12/72 [05:53<34:19, 34.33s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "no GWAS: rs62480001\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\r",
      " 18%|█▊        | 13/72 [06:52<39:48, 40.48s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "no GWAS: rs817352\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\r",
      " 19%|█▉        | 14/72 [07:11<33:43, 34.89s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "no GWAS: rs80164297\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 24%|██▎       | 17/72 [09:23<37:43, 41.16s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "no GWAS: rs11772922\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 26%|██▋       | 19/72 [10:43<35:14, 39.89s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "no GWAS: rs3758833\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 29%|██▉       | 21/72 [11:39<28:28, 33.49s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "no GWAS: rs11047696\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\r",
      " 31%|███       | 22/72 [12:09<27:15, 32.70s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "no GWAS: rs9971029\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\r",
      " 32%|███▏      | 23/72 [12:35<24:55, 30.53s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "no GWAS: rs4949655\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 42%|████▏     | 30/72 [16:56<26:11, 37.41s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "no GWAS: rs6007595\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\r",
      " 43%|████▎     | 31/72 [17:27<24:21, 35.64s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "no GWAS: rs7309189\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\r",
      " 44%|████▍     | 32/72 [18:20<27:11, 40.79s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "no GWAS: rs9657360\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 49%|████▊     | 35/72 [19:43<20:09, 32.70s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "no GWAS: rs731835\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 53%|█████▎    | 38/72 [26:08<51:53, 91.57s/it]   "
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "no GWAS: rs260503\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 58%|█████▊    | 42/72 [28:04<22:59, 46.00s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "no GWAS: rs13140099\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\r",
      " 60%|█████▉    | 43/72 [28:34<19:51, 41.10s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "no GWAS: rs2235910\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 71%|███████   | 51/72 [42:19<40:20, 115.26s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "no GWAS: rs1628955\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 74%|███████▎  | 53/72 [43:55<25:05, 79.24s/it] "
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "no GWAS: rs12443580\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 82%|████████▏ | 59/72 [48:18<09:34, 44.16s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "no GWAS: rs150458741\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 85%|████████▍ | 61/72 [49:39<07:29, 40.88s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "no GWAS: rs62423804\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\r",
      " 86%|████████▌ | 62/72 [50:16<06:36, 39.69s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "no GWAS: rs2267989\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 89%|████████▉ | 64/72 [50:56<03:58, 29.82s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "no GWAS: rs7605964\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 99%|█████████▊| 71/72 [54:21<00:24, 24.54s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "no GWAS: rs1261896\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 72/72 [54:53<00:00, 45.75s/it]\n"
     ]
    }
   ],
   "source": [
    "# curl -k -H \"Content-Type: application/json\" -X POST -d '{\"snps\": \"rs3\\nrs4\", \"pop\": \"YRI\", \"r2_d\": \"r2\", \"r2_d_threshold\": \"0.1\", \"window\": \"500000\", \"genome_build\": \"grch37\"}' 'https://ldlink.nci.nih.gov/LDlinkRest/ldtrait?token=faketoken123'\n",
    "# snp = \"rs10276099\"\n",
    "for snp in tqdm(allcelltypes_snps):\n",
    "    if os.path.exists(savedir/f'{snp}.tsv'):\n",
    "        continue\n",
    "    else:\n",
    "        params = {\"snps\": snp, \n",
    "                  \"pop\": \"CEU\", \n",
    "                  \"r2_d\": \"r2\", \n",
    "                  \"r2_d_threshold\": \"0.8\", \n",
    "                  \"window\": \"500000\", \n",
    "                  \"genome_build\": \"grch37\"}\n",
    "        r = requests.request(headers={\"Content-Type\": \"application/json\"},\n",
    "                             method='POST',\n",
    "                             json=params, \n",
    "                             url=f'https://ldlink.nci.nih.gov/LDlinkRest/ldtrait?token={token}')\n",
    "        try:\n",
    "            if \"No entries in the GWAS Catalog are identified using the LDtrait search criteria.\" in r.text:\n",
    "                print('no GWAS:', snp)\n",
    "                continue\n",
    "            else:\n",
    "                r_df = pd.read_csv(StringIO(r.text), sep='\\t')\n",
    "                r_df.to_csv(savedir/f'{snp}.tsv', sep='\\t', index=False)\n",
    "        except:\n",
    "            print('failed entry:', snp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 72/72 [00:00<00:00, 298.96it/s]\n"
     ]
    }
   ],
   "source": [
    "allsnps_inld_gwas_df = pd.DataFrame()\n",
    "for snp in tqdm(allcelltypes_snps):\n",
    "    if os.path.exists(savedir/f'{snp}.tsv'):\n",
    "        df = pd.read_csv(savedir/f'{snp}.tsv', sep='\\t')\n",
    "        if 'error' not in df.iloc[0].values[0]:\n",
    "            allsnps_inld_gwas_df = pd.concat([allsnps_inld_gwas_df, df],\n",
    "                                            axis=0)\n",
    "            \n",
    "allsnps_inld_gwas_df.to_csv(savedir/'summary.tsv', sep='\\t', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "allsnps_inld_gwas_df = pd.read_csv(savedir/'summary.tsv', sep='\\t')\n",
    "magma_df = pd.read_csv(savedir/'coeqtl_with_gwas_and_magma.tsv', sep='\\t')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>VARIABLE</th>\n",
       "      <th>celltype</th>\n",
       "      <th>SNP</th>\n",
       "      <th>gene</th>\n",
       "      <th>TYPE</th>\n",
       "      <th>NGENES</th>\n",
       "      <th>BETA</th>\n",
       "      <th>BETA_STD</th>\n",
       "      <th>SE</th>\n",
       "      <th>P</th>\n",
       "      <th>...</th>\n",
       "      <th>non_effect_allele</th>\n",
       "      <th>current_build</th>\n",
       "      <th>frequency</th>\n",
       "      <th>sample_size</th>\n",
       "      <th>zscore</th>\n",
       "      <th>pvalue</th>\n",
       "      <th>effect_size</th>\n",
       "      <th>standard_error</th>\n",
       "      <th>imputation_status</th>\n",
       "      <th>n_cases</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>B_rs1131017_RPS26</td>\n",
       "      <td>B</td>\n",
       "      <td>rs1131017</td>\n",
       "      <td>RPS26</td>\n",
       "      <td>SET</td>\n",
       "      <td>38</td>\n",
       "      <td>-0.199320</td>\n",
       "      <td>-0.008952</td>\n",
       "      <td>0.12542</td>\n",
       "      <td>0.943980</td>\n",
       "      <td>...</td>\n",
       "      <td>G</td>\n",
       "      <td>hg38</td>\n",
       "      <td>0.580808</td>\n",
       "      <td>54612</td>\n",
       "      <td>0.138937</td>\n",
       "      <td>0.889500</td>\n",
       "      <td>0.002200</td>\n",
       "      <td>0.015600</td>\n",
       "      <td>original</td>\n",
       "      <td>17008.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>B_rs1131017_RPS26</td>\n",
       "      <td>B</td>\n",
       "      <td>rs1131017</td>\n",
       "      <td>RPS26</td>\n",
       "      <td>SET</td>\n",
       "      <td>38</td>\n",
       "      <td>0.201320</td>\n",
       "      <td>0.009042</td>\n",
       "      <td>0.12905</td>\n",
       "      <td>0.059382</td>\n",
       "      <td>...</td>\n",
       "      <td>G</td>\n",
       "      <td>hg38</td>\n",
       "      <td>0.580808</td>\n",
       "      <td>53293</td>\n",
       "      <td>1.735682</td>\n",
       "      <td>0.082620</td>\n",
       "      <td>0.023902</td>\n",
       "      <td>0.013700</td>\n",
       "      <td>original</td>\n",
       "      <td>19099.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>B_rs1131017_RPS26</td>\n",
       "      <td>B</td>\n",
       "      <td>rs1131017</td>\n",
       "      <td>RPS26</td>\n",
       "      <td>SET</td>\n",
       "      <td>37</td>\n",
       "      <td>0.163610</td>\n",
       "      <td>0.007256</td>\n",
       "      <td>0.12608</td>\n",
       "      <td>0.097201</td>\n",
       "      <td>...</td>\n",
       "      <td>G</td>\n",
       "      <td>hg38</td>\n",
       "      <td>0.580808</td>\n",
       "      <td>29344</td>\n",
       "      <td>-2.348664</td>\n",
       "      <td>0.018841</td>\n",
       "      <td>-0.010569</td>\n",
       "      <td>0.004363</td>\n",
       "      <td>original</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>B_rs1131017_RPS26</td>\n",
       "      <td>B</td>\n",
       "      <td>rs1131017</td>\n",
       "      <td>RPS26</td>\n",
       "      <td>SET</td>\n",
       "      <td>38</td>\n",
       "      <td>-0.010395</td>\n",
       "      <td>-0.000467</td>\n",
       "      <td>0.11668</td>\n",
       "      <td>0.535490</td>\n",
       "      <td>...</td>\n",
       "      <td>G</td>\n",
       "      <td>hg38</td>\n",
       "      <td>0.580808</td>\n",
       "      <td>15954</td>\n",
       "      <td>-0.324182</td>\n",
       "      <td>0.745800</td>\n",
       "      <td>-0.009950</td>\n",
       "      <td>0.025700</td>\n",
       "      <td>original</td>\n",
       "      <td>7387.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>B_rs1131017_RPS26</td>\n",
       "      <td>B</td>\n",
       "      <td>rs1131017</td>\n",
       "      <td>RPS26</td>\n",
       "      <td>SET</td>\n",
       "      <td>38</td>\n",
       "      <td>0.282350</td>\n",
       "      <td>0.012677</td>\n",
       "      <td>0.11706</td>\n",
       "      <td>0.007937</td>\n",
       "      <td>...</td>\n",
       "      <td>G</td>\n",
       "      <td>hg38</td>\n",
       "      <td>0.580808</td>\n",
       "      <td>337159</td>\n",
       "      <td>-1.597883</td>\n",
       "      <td>0.110069</td>\n",
       "      <td>-0.000210</td>\n",
       "      <td>0.000132</td>\n",
       "      <td>original</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 44 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            VARIABLE celltype        SNP   gene TYPE  NGENES      BETA  \\\n",
       "0  B_rs1131017_RPS26        B  rs1131017  RPS26  SET      38 -0.199320   \n",
       "1  B_rs1131017_RPS26        B  rs1131017  RPS26  SET      38  0.201320   \n",
       "2  B_rs1131017_RPS26        B  rs1131017  RPS26  SET      37  0.163610   \n",
       "3  B_rs1131017_RPS26        B  rs1131017  RPS26  SET      38 -0.010395   \n",
       "4  B_rs1131017_RPS26        B  rs1131017  RPS26  SET      38  0.282350   \n",
       "\n",
       "   BETA_STD       SE         P  ... non_effect_allele current_build  \\\n",
       "0 -0.008952  0.12542  0.943980  ...                 G          hg38   \n",
       "1  0.009042  0.12905  0.059382  ...                 G          hg38   \n",
       "2  0.007256  0.12608  0.097201  ...                 G          hg38   \n",
       "3 -0.000467  0.11668  0.535490  ...                 G          hg38   \n",
       "4  0.012677  0.11706  0.007937  ...                 G          hg38   \n",
       "\n",
       "   frequency sample_size    zscore    pvalue effect_size standard_error  \\\n",
       "0   0.580808       54612  0.138937  0.889500    0.002200       0.015600   \n",
       "1   0.580808       53293  1.735682  0.082620    0.023902       0.013700   \n",
       "2   0.580808       29344 -2.348664  0.018841   -0.010569       0.004363   \n",
       "3   0.580808       15954 -0.324182  0.745800   -0.009950       0.025700   \n",
       "4   0.580808      337159 -1.597883  0.110069   -0.000210       0.000132   \n",
       "\n",
       "   imputation_status  n_cases  \n",
       "0           original  17008.0  \n",
       "1           original  19099.0  \n",
       "2           original      NaN  \n",
       "3           original   7387.0  \n",
       "4           original      NaN  \n",
       "\n",
       "[5 rows x 44 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "magma_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "VARIABLE                                       B_rs1131017_RPS26\n",
       "celltype                                                       B\n",
       "SNP                                                    rs1131017\n",
       "gene                                                       RPS26\n",
       "TYPE                                                         SET\n",
       "NGENES                                                        38\n",
       "BETA                                                    -0.19932\n",
       "BETA_STD                                               -0.008952\n",
       "SE                                                       0.12542\n",
       "P                                                        0.94398\n",
       "prefix                                  results/current/magma/AD\n",
       "trait                                                         AD\n",
       "FDR                                                     0.973479\n",
       "Tag                                               IGAP_Alzheimer\n",
       "PUBMED_Paper_Link    http://www.ncbi.nlm.nih.gov/pubmed/24162737\n",
       "Phenotype                                              Alzheimer\n",
       "RSID                                                  rs10876864\n",
       "RSALIAS                                               rs57455456\n",
       "CHR                                                           12\n",
       "POS1                                                    56435929\n",
       "POS2                                                    56401085\n",
       "DIST                                                      -34844\n",
       "R2                                                      0.991789\n",
       "D                                                       0.240643\n",
       "DPRIME                                                  0.995886\n",
       "MAJOR                                                          A\n",
       "MINOR                                                          G\n",
       "MAF                                                     0.408549\n",
       "CMMB                                                    0.155229\n",
       "CM                                                     71.092406\n",
       "panel_variant_id                          chr12_56007301_G_A_b38\n",
       "chromosome                                                 chr12\n",
       "position                                                56007301\n",
       "effect_allele                                                  A\n",
       "non_effect_allele                                              G\n",
       "current_build                                               hg38\n",
       "frequency                                               0.580808\n",
       "sample_size                                                54612\n",
       "zscore                                                  0.138937\n",
       "pvalue                                                    0.8895\n",
       "effect_size                                               0.0022\n",
       "standard_error                                            0.0156\n",
       "imputation_status                                       original\n",
       "n_cases                                                  17008.0\n",
       "Name: 0, dtype: object"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "magma_df.iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[0.041321, 'Inflammatory Bowel Disease'],\n",
       "       [0.030935, 'Non-cancer illness code, self-reported: psoriasis'],\n",
       "       [0.0090688,\n",
       "        'Non-cancer illness code, self-reported: schizophrenia'],\n",
       "       [0.0042454,\n",
       "        'Overall breast cancer in Europeans, imputed genotype'],\n",
       "       [0.032584, 'Diagnoses - main ICD10: G40 Epilepsy'],\n",
       "       [0.0013766,\n",
       "        'Estrogen-receptor-negative breast cancer in Europeans, imputed genotype'],\n",
       "       [0.025212,\n",
       "        'Non-cancer illness code, self-reported: high cholesterol']],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "magma_df[(magma_df['SNP']=='rs4147638') & (magma_df['P']<0.05)][['P', 'Phenotype']].values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Query</th>\n",
       "      <th>GWAS Trait</th>\n",
       "      <th>RS Number</th>\n",
       "      <th>Position (GRCh37)</th>\n",
       "      <th>Alleles</th>\n",
       "      <th>R2</th>\n",
       "      <th>D'</th>\n",
       "      <th>Risk Allele</th>\n",
       "      <th>Effect Size (95% CI)</th>\n",
       "      <th>Beta or OR</th>\n",
       "      <th>P-value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>rs2954654</td>\n",
       "      <td>Type 2 diabetes</td>\n",
       "      <td>rs2294120</td>\n",
       "      <td>chr8:146003567</td>\n",
       "      <td>A=0.52, G=0.48</td>\n",
       "      <td>0.846295</td>\n",
       "      <td>0.957895</td>\n",
       "      <td>0.455879299759268</td>\n",
       "      <td>0.04430</td>\n",
       "      <td>0.029-0.06</td>\n",
       "      <td>2.000000e-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>rs4840568</td>\n",
       "      <td>Albumin-globulin ratio</td>\n",
       "      <td>rs2409780</td>\n",
       "      <td>chr8:11337587</td>\n",
       "      <td>C=0.237, T=0.763</td>\n",
       "      <td>0.897156</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>NR</td>\n",
       "      <td>0.04604</td>\n",
       "      <td>0.035-0.057</td>\n",
       "      <td>1.000000e-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>rs4840568</td>\n",
       "      <td>Non-albumin protein levels</td>\n",
       "      <td>rs2409780</td>\n",
       "      <td>chr8:11337587</td>\n",
       "      <td>C=0.237, T=0.763</td>\n",
       "      <td>0.897156</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>NR</td>\n",
       "      <td>0.04456</td>\n",
       "      <td>0.034-0.055</td>\n",
       "      <td>1.000000e-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>rs4840568</td>\n",
       "      <td>Rheumatoid arthritis</td>\n",
       "      <td>rs2618444</td>\n",
       "      <td>chr8:11338370</td>\n",
       "      <td>A=0.763, C=0.237</td>\n",
       "      <td>0.897156</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>NR</td>\n",
       "      <td>0.10050</td>\n",
       "      <td>0.072-0.129</td>\n",
       "      <td>7.000000e-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>rs4840568</td>\n",
       "      <td>Systemic lupus erythematosus</td>\n",
       "      <td>rs2618444</td>\n",
       "      <td>chr8:11338370</td>\n",
       "      <td>A=0.763, C=0.237</td>\n",
       "      <td>0.897156</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>NR</td>\n",
       "      <td>1.36000</td>\n",
       "      <td>1.22-1.51</td>\n",
       "      <td>7.000000e-09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Query                    GWAS Trait  RS Number Position (GRCh37)  \\\n",
       "0  rs2954654               Type 2 diabetes  rs2294120    chr8:146003567   \n",
       "1  rs4840568        Albumin-globulin ratio  rs2409780     chr8:11337587   \n",
       "2  rs4840568    Non-albumin protein levels  rs2409780     chr8:11337587   \n",
       "3  rs4840568          Rheumatoid arthritis  rs2618444     chr8:11338370   \n",
       "4  rs4840568  Systemic lupus erythematosus  rs2618444     chr8:11338370   \n",
       "\n",
       "            Alleles        R2        D'        Risk Allele  \\\n",
       "0    A=0.52, G=0.48  0.846295  0.957895  0.455879299759268   \n",
       "1  C=0.237, T=0.763  0.897156  1.000000                 NR   \n",
       "2  C=0.237, T=0.763  0.897156  1.000000                 NR   \n",
       "3  A=0.763, C=0.237  0.897156  1.000000                 NR   \n",
       "4  A=0.763, C=0.237  0.897156  1.000000                 NR   \n",
       "\n",
       "   Effect Size (95% CI)   Beta or OR       P-value  \n",
       "0               0.04430   0.029-0.06  2.000000e-08  \n",
       "1               0.04604  0.035-0.057  1.000000e-16  \n",
       "2               0.04456  0.034-0.055  1.000000e-15  \n",
       "3               0.10050  0.072-0.129  7.000000e-12  \n",
       "4               1.36000    1.22-1.51  7.000000e-09  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "allsnps_inld_gwas_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "allsnps_inld_gwas_df.to_excel('./coeqtl_mapping/output/snps_in_ld_with_gwas_catelogue.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
